﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqlCeDriver
{
	/*
		Class to help split up the implementation of the SQL server driver
		This handles the nitty gritty of assembling the SQL and marking up the DataTable
	*/
	class TableCommandProcessor
	{
		internal TableCommandProcessor(SqlCeDataSource parent)
		{
			this.dataSource = parent;
		}

		/*
			Convert the view definition into SQL. This for the table data
		*/
		internal String TableDataCommandString(DbView.ViewContext context, String filterClause)
		{
			// getuseful stuff from the context
			DbView.ViewDefinition vp = context.ViewDef;
			String tableName = this.TableNameFromContext(context);
			// delegate to original version
			return this.TableDataCommandString(tableName, vp, filterClause);
		}

		/*
			Called directly to generate a sql string for updates.
		*/
		internal String TableDataCommandString(String tableName, DbView.ViewDefinition vp, String filterClause)
		{
			// on initial display of data table there won't be a view definition yet
			if (vp == null)
				// bad sql, but it saves a separate query for fields.
				return "select top (500) * from " + Helper.SafeTableName(tableName);

			// from here building Sql based on the current view definition.
			DbView.SchemaField[] display = vp.DisplayFields;
			String fieldList = "";
			foreach (DbView.SchemaField f in display)
			{
				if (fieldList.Length > 0) fieldList += ",";
				fieldList += f.SqlSafeName;
			}
			DbView.SchemaField[] sort = vp.SortFields;
			String sortClause = "";
			foreach (DbView.SchemaField f in sort)
			{
				if (sortClause.Length > 0) sortClause += ",";
				sortClause += f.SqlSafeName;
				if (f.Ord == "D")
					sortClause += " DESC";
			}
			if (sortClause.Length > 0) sortClause = " ORDER BY "+sortClause;

			String whereClause = "";

			// filter text search 
			if (filterClause.Length > 0)
			{
				// numeric test
				bool isNumeric = this.IsNumericSearch(filterClause);
				// filter clause logic
				DbView.SchemaField[] search = vp.SearchFields;
				if (isNumeric)
				{
					foreach (DbView.SchemaField f in search)
					{
						if (DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							whereClause += String.Format("{0} = {1}", f.SqlSafeName, filterClause);
						}
					}
				}
				else
				{
					foreach (DbView.SchemaField f in search)
					{
                        if (!DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							// create a special form clause that can be modified to provide case-insensitive searching
							// in a case-sensitive database
							String clause = String.Format("[[UPPER({0}) like '%%']]", f.SqlSafeName);
							clause = SqlCeTemplates.Templates.ProcessFilterClause(clause, filterClause, this.dataSource.IsCaseSensitive);
							whereClause += clause;
							// WhereClause += String.Format("{0} like '%{1}%'", f.Name, FilterClause);
						}
					}
				}
				if (whereClause.Length > 0) whereClause = String.Format("({0})", whereClause);
			}
			// view definition extra search clause
			if (vp.ExtraClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.ExtraClause + ")";
			}

			// additional ad-hoc filter clause
			if (vp.FilterClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.FilterClause + ")";
			}
			
			if (whereClause.Length > 0) whereClause = " where " + whereClause;
			String sqlTemplate = @"
select top(500) {1} 
  from {0}
  {2}
  {3}
";
			String sql = string.Format(sqlTemplate, Helper.SafeTableName(tableName), fieldList, whereClause, sortClause);
			return sql;
		}

		internal String MakeFieldsCommandString(DbView.ViewContext context, String sqlTemplate, String filterString)
		{
			// grab the table from the view context
			String tableName = this.TableNameFromContext(context);
			// assemble actual SQL from the template, table name and filter parameter.
			return String.Format(sqlTemplate, tableName, filterString);
		}

		// just need the table
		internal String MakeFieldDescriptionsCommandString(String tableName, String sqlTemplate, String filterString)
		{
			String command = String.Format(sqlTemplate, tableName);
			return AddFilterClause(command, filterString);
		}

		internal String MakePermissionsCommand(String commandTemplate, DbView.Command command, String filterString)
		{
			String paramValue = (command["SUMMARY"] == "Y") ? "1" : "0";  // convert from Y/N to SQL where clause value
			return AddFilterClause(String.Format(commandTemplate, paramValue), filterString);
		}
		
		internal String MakeArticlesCommand(String commandTemplate, DbView.Command command, String filterString)
		{
			String extraClause = ""; // if from the main menu
			String objectName = command[DbView.Command.TableParameterName(0)];
			if (command["PUB"] == "Y")
			{
				// see {1} in ARTICLES_SQLTEMPLATE
				extraClause = String.Format("and (upper(p.name) = '{0}' or upper(p.name) = '{0}')", objectName);
			}
			String newTemplate = AddFilterClause(commandTemplate, filterString);
			return String.Format(newTemplate, extraClause);
		}

		internal String AggregateDataCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			int top = 1000;
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String nullField = this.MakeNullProxy(field);
            String filterClause = this.MakeFilterClause(context); 
			return String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(), 
			                                             filterClause, top);
		}

		internal String RowCountCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String filterClause = this.MakeFilterClause(context); // later.
			return String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(),
														filterClause);
		}

		//internal String MakeDependenciesCommand(String commandTemplate, DbView.Command command, String filterString)
		//{
		//    String extraClause = ""; // if from the main menu
		//    String objectName = command[DbView.Command.TableParameterName(0)];
		//    if (objectName.Length > 0)
		//    {
		//        extraClause = String.Format("and (o2.object_id = object_id('{0}') or o.object_id = object_id('{0}'))", objectName);
		//    }
		//    return String.Format(commandTemplate, filterString, extraClause);
		//}

		//////////////////////////////////////////////////////////////////////////////////////////////
		// The data-fetch part. This is called from the bakground thread kicked off by 
		// Mainpresenter.PopulateGrid() for any command that returns a table
		// 
		public DataTable BuildDataTable(DbView.Command command, String commandString, DbView.ViewContext context)
		{
			String tableName = this.TableNameFromContext(context);
			String connectionString = this.dataSource.ConnectionString;
			/* Hacky override for table sizes to force the name of the DataTable to "Tables"
			 * This because of another hacky function;MainPresenter.GetTableName() that
			 * uses a variety of means to infer a true database table name for functions
			 * such as field lists and data etc. One strategy is to test the DataTable name
			 * for the text "Tables" and use the column 1 value. Perhaps better would be to 
			 * decorate the DataTable with extra info.
			*/
			DataTable table = new DataTable();
			
			try
			{
				// New for 2012. QuickDiff has already built the table
				// Quick diff is not implemented in CE. May be useful
				//if (Command.Cmd == DbView.Commands.cmd.Extension && Command.Name == Cmd.DisplayHeldTable)
				//{
				//    int tableId = GetHeldTableId(Command);
				//    DSet.Tables.Add(DataTableRepository.Instance.GetTable(tableId)); 
				//}
				//else
					// Overwhelmingly normal branch is here where a SQL command is issued to the database.
					table = new Helper(this.dataSource.GetAccessor()).GenericQuery(connectionString, commandString, tableName);
			}
			catch (Exception)
			{
				throw;  // rethrow an unanticipated SQL error (timeout, syntax error etc.
			}
			// Following data=retrieval we can now use DataTable properties to mark-up the table in a way that the
			// display grid can recognise. Hence we can colour columns. rows and cells to make our point clearer.
				
			// [Specialise: markup columns to be added as pop-up command parameters ]
			DbView.DataTableExtraInfoManager mgr = new DbView.DataTableExtraInfoManager(table);
			mgr.Command = commandString;
			//
			if (command.Cmd == DbView.Commands.Cmnd.TableData)
			{
				// decorate. This adds the coloured columns for PKey, FKey and Indexes.
				this.AddColumnMetaData(mgr);
			}
			// add below as required later. remove when its known not to be needed
			//// below flags up other columns required as parameters for some pop-ups.
			if (command.Cmd == DbView.Commands.Cmnd.ForeignKeys)
			{
			    mgr.SetParam(table.Columns[4], CmdParam.FkName);
			    mgr.SetParam(table.Columns[0], CmdParam.FkTable);
			    mgr.SetParam(table.Columns[1], CmdParam.FkField);
			    mgr.SetParam(table.Columns[2], CmdParam.PkTable);
			    mgr.SetParam(table.Columns[3], CmdParam.PkField);
			}
			//if (Command.Cmd == DbView.Commands.cmd.Extension && Command.Name == Cmd.Descriptions)
			//{
			//    mgr.SetParam(table.Columns[1], "DESC");
			//}
			//if (Command.Cmd == DbView.Commands.cmd.Extension && Command.Name == Cmd.Jobs)
			//{
			//    mgr.SetParam(table.Columns[2], "JOBID");
			//}
			//if (Command.Cmd == DbView.Commands.cmd.Dependencies)
			//{
			//    mgr.SetParam(table.Columns[2], "HIGHLIGHT"); // flags the dependent object column for highlight in source
			//}
			//if (Command.Cmd == DbView.Commands.cmd.Extension && Command.Name == Cmd.Owners)
			//{
			//    mgr.SetParam(table.Columns[1], "TYPE");
			////}

			return table;
		}

		//private String TryAltAggregate(string tableName, DbView.Command Command, string filter, out DataSet DSet)
		//{
		//    String connectionString = m_Source.ConnectionString;
		//    int top = 1000;
		//    String template = SqlServerTemplates.AltAggregateTemplate(this.m_Source.SqlVersion);
		//    template = SqlServerTemplates.Templates.ProcessFilterClause(template, filter, m_Source.IsCaseSensitive);
		//    String field = Command["FIELD"];
		//    String commandString = String.Format(template, field, tableName, filter, top);
		//    Helper.GenericMakeDset(connectionString, commandString, tableName, out DSet);
		//    return commandString; // return this so that it can be hooked onto the data table
		//}

		//private String TryAltTables(String tableName, String filter, out DataSet DSet)
		//{
		//    String connectionString = m_Source.ConnectionString;
		//    String template = SqlServerTemplates.AltTableTemplate(this.m_Source.SqlVersion);
		//    template = SqlServerTemplates.Templates.ProcessFilterClause(template, filter, m_Source.IsCaseSensitive);
		//    String commandString = String.Format(template, filter);
		//    Helper.GenericMakeDset(connectionString, commandString, tableName, out DSet);
		//    return commandString; // return this so that it can be hooked onto the data table
		////}

		int GetHeldTableId(DbView.Command command)
		{
			String strId = command[CmdParam.TableId];
			int id = 0;
			if (!int.TryParse(strId, out id))
				throw new Exception("Failed to find table data");
			return id;
		}

		void AddColumnMetaData(DbView.DataTableExtraInfoManager mgr)
		{
			String fieldnameColumn = "ColumnName";
			String connectionString = this.dataSource.ConnectionString;
			// need a query
			String commandString = this.dataSource.GetSchemaSql(mgr.Table.TableName);
			DataTable table =new Helper(this.dataSource.GetAccessor()).GenericQuery(connectionString, commandString, mgr.Table.TableName);
			
			// check for pkeys
			int pkeyCount = 0;
			foreach (DataRow row in table.Rows)
				if (row["PKey?"].ToString().Length > 2) ++pkeyCount;
			bool modifiable = (pkeyCount > 0); // can't modify a table w/o a pkey
			// mark up pkeys, indexes and fkeys for colouring
			foreach (DataColumn col in mgr.Table.Columns)
			{
				String search = String.Format("[{1}]='{0}'", col.ColumnName, fieldnameColumn);
				DataRow [] row = table.Select(search);
				if (row.GetLength(0) > 0)
				{
					if (row[0]["PKey?"].ToString().Length > 2)
					{
						mgr.SetPkey(col);
						--pkeyCount; // counting down to 0
					}
					if (row[0]["FKey?"].ToString().Length > 2)
						mgr.SetFkey(col);
					if (row[0]["Index?"].ToString().Length > 2)
						mgr.SetIndexed(col);
				}
				if (modifiable && pkeyCount == 0) // not all pkeys are in the view
					mgr.SetModifiable();
			}
		}

		///////////////////////////////////////////////////////////////////////
		// Helpers
		// used by parent 
		internal String AddFilterClause(String command, String filterText)
		{
			// delegate to template processor
			if (HasModifiedWhereClause(command))
				return SqlCeTemplates.Templates.ProcessFilterClause(command, filterText, this.dataSource.IsCaseSensitive);
			return String.Format(command, filterText); // what about Case sensitive.
		}

		static internal String AddFilterClause(String command, String filterText, bool isCaseSensitive)
		{
			// delegate to template processor
			if (HasModifiedWhereClause(command))
				return SqlCeTemplates.Templates.ProcessFilterClause(command, filterText, isCaseSensitive);
			return String.Format(command, filterText); // what about Case sensitive.
		}

		// Detecting templates where the only configuration is the filter string
		internal bool SimpleTemplate(String commandTemplate)
		{
			if (commandTemplate.Length == 0) return false; // don't know what this condition is about - bad!
			// for fancy filters the filterstring is not added by string format
			if (HasModifiedWhereClause(commandTemplate))
			{
				if (commandTemplate.IndexOf("{0}") == -1)
					return true;
			}
			else
				// {0} is for the filter string therefore if there is no {1} this is simple
				if (commandTemplate.IndexOf("{1}") == -1)
					return true;
			return false;
		}

		// Templates include special mark-up this knows that [[ ]] encloses a where clause that has a variety of flavours. See below
		static bool HasModifiedWhereClause(String commandTemplate) { return (commandTemplate.IndexOf("[[") > 0); }
		
		String TableNameFromContext(DbView.ViewContext context)
		{
			// if there is a non-null view definition the table is 
			// there.
			if (context.ViewDef != null) return context.ViewDef.TableName;
			// otherwise this is (hopefully) a popup from the table list
			else return context.ObjectName;
		}

		bool IsNumericSearch(String filter)
		{
			Int64 bigint;
			return Int64.TryParse(filter, out bigint);
		}

        private string MakeFilterClause(DbView.ViewContext context)
        {
            // add the Filter clause.
            DbView.ViewDefinition vp = context.ViewDef;
            String extraClause = vp.ExtraClause;
            if (extraClause.Length > 0 && vp.FilterClause.Length > 0)
                extraClause += " AND ";
            if (vp.FilterClause.Length > 0)
                extraClause += vp.FilterClause;
            if (extraClause.Length == 0) return "";
            return " AND " + extraClause;
        }

		private String MakeNullProxy(DbView.SchemaField field)
		{
			return (field.Type == "S") ? "`" : // choose an unlikely character.
			(field.Type == "I") ? "-8989" :  // random very unusual value.
            (field.Type == "J") ? "-89898989" :  // random very unusual value (bigint)
            (field.Type == "N") ? "-8989.9898" :  // random very unusual value.
			(field.Type == "X") ? "239" :     // will be inaccurate, but no choice
			(field.Type == "D") ? "19700102" 
			                   : "0"; // this should suit all
		}

		SqlCeDataSource dataSource;
	}
}
